import modules.mysql_packages as st

# 将数据库数据按照id分组并按照时间进行排序
def process_data(data_list):
    # 1. 按账户ID分组
    id_group = {}
    for item in data_list:
        account_id = item['账户ID']
        if account_id not in id_group:
            id_group[account_id] = []
        # 提取需要的字段并添加到对应id的列表中
        if "奇迹岛" in item["主体"] or "爆单" in item["主体"]:
                id_group[account_id].append({
                '更新时间': item['更新时间'],
                '总消耗': item['总消耗'],
                '转化数': item['转化数'],
                'CPA_转化成本': item['CPA_转化成本'],
                'ROSE_支付完成广告支出回报率_站内': item['ROSE_支付完成广告支出回报率_站内'],
                'CTR': item['CTR'],
                'CVR': item['CVR'],
                "账户名称":item["账户名称"],
                "主体":item["主体"]
            })
        elif "米壳主体" in item["主体"]:
            id_group[account_id].append({
                '更新时间': item['更新时间'],
                '总消耗': item['总消耗'],
                '转化数': item['转化数'],
                'CPA_转化成本': item['CPA_转化成本'],
                'ROSE_支付完成广告支出回报率_站内': item['ROSE_支付完成广告支出回报率_站内'],
                'CTR': item['CTR'],
                'CPC': item['CPC'],
                "账户名称": item["账户名称"],
                "主体": item["主体"],
                "展示次数":item["展示次数"],
                "点击量":item["点击量"]
            })

    # 2. 对每个id下的数据按更新时间排序
    sorted_result = []
    for account_id, data in id_group.items():
        # 按更新时间排序（字符串格式按字典序排序，适用于标准时间格式）
        sorted_data = sorted(data, key=lambda x: x['更新时间'])
        sorted_result.append({
            '账户id': account_id,
            '数据按时间排序': sorted_data
        })

    return sorted_result


# 获取最新的两个总体数据
def get_last_data(total_dict_list):
    # 1. 按主体分组
    zt_group = {}
    for item in total_dict_list:
        account_id = item['主体']
        if account_id not in zt_group:
            zt_group[account_id] = []
        if "奇迹岛" in account_id or "爆单" in account_id:
            # 提取需要的字段并添加到对应id的列表中
            zt_group[account_id].append({
                '更新时间': item['更新时间'],
                '总消耗': item['总消耗'],
                '转化数': item['转化数'],
                'CPA_转化成本': item['CPA_转化成本'],
                'ROSE_支付完成广告支出回报率_站内': item['ROSE_支付完成广告支出回报率_站内'],
                'CTR': item['CTR'],
                'CVR': item['CVR']
            })
        elif "米壳主体" in account_id:
            # 提取需要的字段并添加到对应id的列表中
            zt_group[account_id].append({
                '更新时间': item['更新时间'],
                '总消耗': item['总消耗'],
                '转化数': item['转化数'],
                'CPA_转化成本': item['CPA_转化成本'],
                'ROSE_支付完成广告支出回报率_站内': item['ROSE_支付完成广告支出回报率_站内'],
                'CTR': item['CTR'],
                'CPC': item['CPC'],
                "展示次数": item["展示次数"],
                "点击量": item["点击量"]
            })
    sorted_result = []
    for account_id, data in zt_group.items():
        # 按更新时间排序（字符串格式按字典序排序，适用于标准时间格式）
        sorted_data = sorted(data, key=lambda x: x['更新时间'])
        sorted_result.append({
            '主体': account_id,
            '数据按时间排序': sorted_data
        })
    return sorted_result

# 数据比对与预警
def check_alter():
    connection = st.create_database_connection("47.107.69.10", "root", "xxyo7qf1fn", "bksy_company",port=4728)
    # 查询爆单而已数据
    bd_dict_list = st.sql_select(connection, "select * from `爆单data`")
    # 查询奇迹岛data
    qjd_dict_list = st.sql_select(connection, "select * from `奇迹岛data`")
    # 查询米壳主体data
    mkzt_dict_list = st.sql_select(connection, "select * from `米壳主体data`")
    # 查询总体数据
    total_dict_list = st.sql_select(connection, "select * from `TK_整体据统计`")
    # 分组排序
    bd_id_data_list = process_data(bd_dict_list)
    qjd_id_data_list = process_data(qjd_dict_list)
    mkzt_id_data_list = process_data(mkzt_dict_list)
    total_data_list = get_last_data(total_dict_list)
    # 总体数据判断
    total_list = []
    mkzt_total = []
    for index, item in enumerate(total_data_list):
        current_zt = item["主体"]
        current_data_list = item["数据按时间排序"]
        if "米壳主体" in current_zt:
            mkzt_total.append({"col_4j5vk040i8k":current_data_list[-1]["更新时间"],
                               "customer_name": "米壳概览-USB风扇",
                               "customer_scale":"",
                               "customer_arr":round(float(current_data_list[-1]["总消耗"]),2),
                               "col_4j5vk032i7l":current_data_list[-1]["转化数"],
                               "col_w0xrt90kluk":current_data_list[-1]["CPA_转化成本"],
                               "col_hp2cvl5rlke":round(float(current_data_list[-1]["ROSE_支付完成广告支出回报率_站内"]),2),
                               "col_crqfetc5tq":f'{round(float(current_data_list[-1]["CTR"]),2)}%',
                               "col_2ovmac5v06n":f'{round(float(current_data_list[-1]["CPC"]),2)}',
                               "col_3ovmac9v96n":current_data_list[-1]["点击量"],
                               "col_7ovmac5v08n":current_data_list[-1]["展示次数"]
                               })
            continue
        if len(current_data_list) >= 2:
            total_list.append({
                "col_4j5vk040i8k":current_data_list[-1]["更新时间"],
                "customer_name": current_zt,
                "customer_scale": round(float(current_data_list[-1]["总消耗"]),2),
                "customer_arr": round(float(current_data_list[-1]["总消耗"])-float(current_data_list[-2]["总消耗"]),2),
                "col_4j5vk032i7l": current_data_list[-1]["转化数"],
                "col_w0xrt90kluk": int(current_data_list[-1]["转化数"])-int(current_data_list[-2]["转化数"]),
                "col_hp2cvl5rlke": round(float(current_data_list[-1]["CPA_转化成本"]),2),
                "col_crqfetc5tq": round(float(current_data_list[-1]["CPA_转化成本"])-float(current_data_list[-2]["CPA_转化成本"]),2),
                "col_2ovmac5v06n": round(float(current_data_list[-1]["ROSE_支付完成广告支出回报率_站内"]),2),
                "col_12nt9ko8kaz": round(float(current_data_list[-1]["ROSE_支付完成广告支出回报率_站内"])-float(current_data_list[-2]["ROSE_支付完成广告支出回报率_站内"]),2),
                "col_kd2gavnshnb": f'{round(float(current_data_list[-1]["CTR"]),2)}%',
                "col_94gre8bx6d9": f'{round(float(current_data_list[-1]["CTR"])-float(current_data_list[-2]["CTR"]),2)}%',
                "col_mut4v893ml": f'{round(float(current_data_list[-1]["CVR"]),2)}%',
                "col_msykpigl6oq": f'{round(float(current_data_list[-1]["CVR"])-float(current_data_list[-2]["CVR"]),2)}%',
            })
    # 判断数据是否需要预警通知

    message_list = []
    # message = "【预警通知】\n___________________________\n<预警规则>\n【cpa>0.2,rose<-0.2,总消耗>=10,转化量>=5】\n___________________________\n"
    # bd_meaasge = f"<爆单而已>最近异常账户：\n"
    for index,item in enumerate(bd_id_data_list):
        current_id = item["账户id"]
        current_data_list = item["数据按时间排序"]
        if len(current_data_list) >= 2:
            current_message = f"【{current_id}】"
            gap_total_spend = round(float(current_data_list[-1]["总消耗"]) - float(current_data_list[-2]["总消耗"]),2) # 最近的两个总消耗的数据差
            gap_count_conversion = round(float(current_data_list[-1]["转化数"]) - float(current_data_list[-2]["转化数"]),2)  # 最近两个转化数之差
            gap_cpa = round(float(current_data_list[-1]["CPA_转化成本"]) - float(current_data_list[-2]["CPA_转化成本"]),2)  # 最近两个CPA之差
            gap_rose = round(float(current_data_list[-1]["ROSE_支付完成广告支出回报率_站内"]) - float(current_data_list[-2]["ROSE_支付完成广告支出回报率_站内"]),2)  # 最近两个ROSE之差
            gap_ctr = round(float(current_data_list[-1]["CTR"]) - float(current_data_list[-2]["CTR"]), 2)  # 最近两个CPA之差
            gap_cvr = round(float(current_data_list[-1]["CVR"]) - float(current_data_list[-2]["CVR"]), 2)  # 最近两个CPA之差
            is_need = False
            if gap_cpa > 0.2:
                current_message = f"{current_message}  CPA变化：{gap_cpa}"
                is_need = True
            if gap_rose < -0.2:
                current_message = f"{current_message}  ROSE变化：{gap_rose}"
                is_need = True
            if gap_total_spend >= 10:
                current_message = f"{current_message}  总消耗变化：{gap_total_spend}"
                is_need = True
            if gap_count_conversion >= 5:
                current_message = f"{current_message}  转化量变化：{gap_count_conversion}"
                is_need = True
            if is_need:
                current_dict = {
                    "col_2cqmiporlj6": current_data_list[-1]["主体"],
                    "customer_name": current_data_list[-1]["账户名称"],
                    "customer_scale": current_id,
                    "customer_arr": current_data_list[-1]["更新时间"],
                    "col_6g6c5oe343j": current_data_list[-1]["总消耗"],
                    "col_ppotysk1l7f": gap_total_spend,
                    "col_dtee9ycd85s": current_data_list[-1]["转化数"],
                    "col_8vmk9p2xqff": gap_count_conversion,
                    "col_6yeio6uxo6t": current_data_list[-1]["CPA_转化成本"],
                    "col_40ebl7h73t5": gap_cpa,
                    "col_83bzrvyq06k": current_data_list[0]["ROSE_支付完成广告支出回报率_站内"],
                    "col_pns7w5xlki9": gap_rose,
                    "col_ft3zf27u7sb": f'{current_data_list[-1]["CTR"]}%',
                    "col_ie9c9f7ttgo": f"{gap_ctr}%",
                    "col_xsoqqkac45e": f'{current_data_list[-1]["CVR"]}%',
                    "col_zd3hywwsbxs": f"{gap_cvr}%"
                }
                message_list.append(current_dict)
                # bd_meaasge = f"{bd_meaasge}{current_message}\n"
    # message = f"{message}{bd_meaasge}"
    # qjd_meaasge = f"<奇迹岛>最近异常账户：\n"
    # qjd_rows_list = []
    for index,item in enumerate(qjd_id_data_list):
        current_id = item["账户id"]
        current_data_list = item["数据按时间排序"]
        # print(current_data_list[-1]["更新时间"])
        if len(current_data_list) >= 2:
            current_message = f"【{current_id}】"
            gap_total_spend = round(float(current_data_list[-1]["总消耗"]) - float(current_data_list[-2]["总消耗"]),2)  # 最近的两个总消耗的数据差
            gap_count_conversion = round(float(current_data_list[-1]["转化数"]) - float(current_data_list[-2]["转化数"]),2)  # 最近两个转化数之差
            gap_cpa = round(float(current_data_list[-1]["CPA_转化成本"]) - float(current_data_list[-2]["CPA_转化成本"]),2)  # 最近两个CPA之差
            gap_rose = round(float(current_data_list[-1]["ROSE_支付完成广告支出回报率_站内"]) - float(current_data_list[-2]["ROSE_支付完成广告支出回报率_站内"]),2)  # 最近两个ROSE之差
            gap_ctr = round(float(current_data_list[-1]["CTR"]) - float(current_data_list[-2]["CTR"]),2)  # 最近两个CPA之差
            gap_cvr = round(float(current_data_list[-1]["CVR"]) - float(current_data_list[-2]["CVR"]), 2)  # 最近两个CPA之差
            is_need = False
            if gap_cpa > 0.2:
                current_message = f"{current_message}  CPA变化：{gap_cpa}"
                is_need = True
            if gap_rose < -0.2:
                current_message = f"{current_message}  ROSE变化：{gap_rose}"
                is_need = True
            if gap_total_spend >= 10:
                current_message = f"{current_message}  总消耗变化：{gap_total_spend}"
                is_need = True
            if gap_count_conversion >= 5:
                current_message = f"{current_message}  转化量变化：{gap_count_conversion}"
                is_need = True

            if is_need:
                current_dict = {
                    "col_2cqmiporlj6": current_data_list[-1]["主体"],
                    "customer_name": current_data_list[-1]["账户名称"],
                    "customer_scale": current_id,
                    "customer_arr": current_data_list[-1]["更新时间"],
                    "col_6g6c5oe343j": current_data_list[-1]["总消耗"],
                    "col_ppotysk1l7f": gap_total_spend,
                    "col_dtee9ycd85s": current_data_list[-1]["转化数"],
                    "col_8vmk9p2xqff": gap_count_conversion,
                    "col_6yeio6uxo6t": current_data_list[-1]["CPA_转化成本"],
                    "col_40ebl7h73t5": gap_cpa,
                    "col_83bzrvyq06k": current_data_list[0]["ROSE_支付完成广告支出回报率_站内"],
                    "col_pns7w5xlki9": gap_rose,
                    "col_ft3zf27u7sb": current_data_list[-1]["CTR"],
                    "col_ie9c9f7ttgo": gap_ctr,
                    "col_xsoqqkac45e": current_data_list[-1]["CVR"],
                    "col_zd3hywwsbxs": gap_cvr
                }
                message_list.append(current_dict)

    for index,item in enumerate(mkzt_id_data_list):
        current_id = item["账户id"]
        current_data_list = item["数据按时间排序"]
        mkzt_total.append({"col_4j5vk040i8k": current_data_list[-1]["更新时间"],
                           "customer_name": current_data_list[-1]["账户名称"],
                           "customer_scale": current_id,
                           "customer_arr": round(float(current_data_list[-1]["总消耗"]), 2),
                           "col_4j5vk032i7l": current_data_list[-1]["转化数"],
                           "col_w0xrt90kluk": current_data_list[-1]["CPA_转化成本"],
                           "col_hp2cvl5rlke": round(float(current_data_list[-1]["ROSE_支付完成广告支出回报率_站内"]),
                                                    2),
                           "col_crqfetc5tq": f'{round(float(current_data_list[-1]["CTR"]), 2)}%',
                           "col_2ovmac5v06n": f'{round(float(current_data_list[-1]["CPC"]), 2)}',
                            "col_3ovmac9v96n": current_data_list[-1]["点击量"],
                            "col_7ovmac5v08n": current_data_list[-1]["展示次数"]
                           })
        # print(current_data_list[-1]["更新时间"])
    #     if len(current_data_list) >= 2:
    #         current_message = f"【{current_id}】"
    #         gap_total_spend = round(float(current_data_list[-1]["总消耗"]) - float(current_data_list[-2]["总消耗"]),2)  # 最近的两个总消耗的数据差
    #         gap_count_conversion = round(float(current_data_list[-1]["转化数"]) - float(current_data_list[-2]["转化数"]),2)  # 最近两个转化数之差
    #         gap_cpa = round(float(current_data_list[-1]["CPA_转化成本"]) - float(current_data_list[-2]["CPA_转化成本"]),2)  # 最近两个CPA之差
    #         gap_rose = round(float(current_data_list[-1]["ROSE_支付完成广告支出回报率_站内"]) - float(current_data_list[-2]["ROSE_支付完成广告支出回报率_站内"]),2)  # 最近两个ROSE之差
    #         gap_ctr = round(float(current_data_list[-1]["CTR"]) - float(current_data_list[-2]["CTR"]),2)  # 最近两个CPA之差
    #         gap_cvr = round(float(current_data_list[-1]["CVR"]) - float(current_data_list[-2]["CVR"]), 2)  # 最近两个CPA之差
    #         is_need = False
    #         if gap_cpa > 0.2:
    #             current_message = f"{current_message}  CPA变化：{gap_cpa}"
    #             is_need = True
    #         if gap_rose < -0.2:
    #             current_message = f"{current_message}  ROSE变化：{gap_rose}"
    #             is_need = True
    #         if gap_total_spend >= 10:
    #             current_message = f"{current_message}  总消耗变化：{gap_total_spend}"
    #             is_need = True
    #         if gap_count_conversion >= 5:
    #             current_message = f"{current_message}  转化量变化：{gap_count_conversion}"
    #             is_need = True
    #
    #         if is_need:
    #             current_dict = {
    #                 "col_2cqmiporlj6": current_data_list[-1]["主体"],
    #                 "customer_name": current_data_list[-1]["账户名称"],
    #                 "customer_scale": current_id,
    #                 "customer_arr": current_data_list[-1]["更新时间"],
    #                 "col_6g6c5oe343j": current_data_list[-1]["总消耗"],
    #                 "col_ppotysk1l7f": gap_total_spend,
    #                 "col_dtee9ycd85s": current_data_list[-1]["转化数"],
    #                 "col_8vmk9p2xqff": gap_count_conversion,
    #                 "col_6yeio6uxo6t": current_data_list[-1]["CPA_转化成本"],
    #                 "col_40ebl7h73t5": gap_cpa,
    #                 "col_83bzrvyq06k": current_data_list[0]["ROSE_支付完成广告支出回报率_站内"],
    #                 "col_pns7w5xlki9": gap_rose,
    #                 "col_ft3zf27u7sb": current_data_list[-1]["CTR"],
    #                 "col_ie9c9f7ttgo": gap_ctr,
    #                 "col_xsoqqkac45e": current_data_list[-1]["CVR"],
    #                 "col_zd3hywwsbxs": gap_cvr
    #             }
    #             message_list.append(current_dict)
    # message = f"{message}{qjd_meaasge}"
    # 降序排列
    total_list = sorted(total_list, key=lambda x: x['customer_scale'], reverse=True)
    message_list = sorted(message_list, key=lambda x: x['col_6g6c5oe343j'], reverse=True)
    mkzt_total = sorted(mkzt_total, key=lambda x: x['customer_arr'], reverse=True)
    return total_list,message_list,mkzt_total


# 将gmv数据按照店铺id和账户id进行分组
def gmv_process_data(data_list):
    # 1. 按账户ID、店铺ID分组
    idd_group = {}
    for item in data_list:
        account_id = item['账户ID']
        shop_id = item['店铺ID']
        if tuple([account_id,shop_id]) not in idd_group:
            idd_group[tuple([account_id,shop_id])] = []
        # 提取需要的字段并添加到对应id的列表中
        idd_group[tuple([account_id,shop_id])].append({
            '更新时间': item['更新时间'],
            '成本': item['成本'],
            '订单数': item['订单数'],
            '平均下单成本': item['平均下单成本'],
            '总收入': item['总收入'],
            'ROI': item['ROI'],
            "账户名称":item["账户名称"],
            "店铺名称":item["店铺名称"]
        })

    # 2. 对每个主键下的数据按更新时间排序
    sorted_result = []
    for id, data in idd_group.items():
        # 按更新时间排序（字符串格式按字典序排序，适用于标准时间格式）
        sorted_data = sorted(data, key=lambda x: x['更新时间'])
        sorted_result.append({
            '主键': id,
            '数据按时间排序': sorted_data
        })

    return sorted_result
def gmv_max_check_alter():
    connection = st.create_database_connection("47.107.69.10", "root", "xxyo7qf1fn", "bksy_company", port=4728)
    # 查询爆单而已数据
    mkzt_gmv_dict_list = st.sql_select(connection, "select * from `米壳主体Gmv_Gax`")
    # 分组排序
    mkzt_id_sort_list = gmv_process_data(mkzt_gmv_dict_list)
    # 判断是否提醒
    # 总体数据判断
    total_list = []
    for index, item in enumerate(mkzt_id_sort_list):
        current_zt = item["主键"]
        current_data_list = item["数据按时间排序"]
        if len(current_data_list) >= 2:
            # is_tx = False
            # for key in current_data_list[0]:
            #     if current_data_list[-1][key] != current_data_list[-2][key]:
            #         is_tx = True
            #         break
            if True:
                total_list.append({
                    "col_4j5vk040i8k":current_data_list[-1]["更新时间"],
                    "customer_name":current_data_list[-1]["账户名称"],
                    "customer_scale":current_data_list[-1]["店铺名称"],
                    "customer_arr":current_data_list[-1]["成本"],
                    "col_4j5vk032i7l":round(float(current_data_list[-1]["成本"])-float(current_data_list[-2]["成本"]),2),
                    "col_w0xrt90kluk":current_data_list[-1]["订单数"],
                    "col_hp2cvl5rlke":round(float(current_data_list[-1]["订单数"])-float(current_data_list[-2]["订单数"]),2),
                    "col_crqfetc5tq":current_data_list[-1]["平均下单成本"],
                    "col_2ovmac5v06n":round(float(current_data_list[-1]["平均下单成本"])-float(current_data_list[-2]["平均下单成本"]),2),
                    "col_12nt9ko8kaz":current_data_list[-1]["总收入"],
                    "col_kd2gavnshnb":round(float(current_data_list[-1]["总收入"])-float(current_data_list[-2]["总收入"]),2),
                    "col_94gre8bx6d9":current_data_list[-1]["ROI"],
                    "col_mut4v893ml":round(float(current_data_list[-1]["ROI"])-float(current_data_list[-2]["ROI"]),2)
                })
    # 降序排列
    total_list = sorted(total_list, key=lambda x: x['customer_arr'], reverse=True)
    return total_list

